* clean INE Atlas de Renta
********************************************************************************
* prelims
********************************************************************************
{
clear all
set more off
cap log close

}
********************************************************************************
* program for common cleaning
********************************************************************************
{
cap program drop common_cleaning
program define common_cleaning 

* replacing special characcters
replace v1 = subinstr(v1,"Ñ","N",.)
replace v1 = subinstr(v1,"Ç","C",.)
replace v1 = subinstr(v1,"Á","A",.)
replace v1 = subinstr(v1,"À","A",.)
replace v1 = subinstr(v1,"Ä","A",.)
replace v1 = subinstr(v1,"È","E",.)
replace v1 = subinstr(v1,"É","E",.)
replace v1 = subinstr(v1,"Í","I",.)
replace v1 = subinstr(v1,"Ó","O",.)
replace v1 = subinstr(v1,"Ò","O",.)
replace v1 = subinstr(v1,"Ö","O",.)
replace v1 = subinstr(v1,"Ú","U",.)
replace v1 = subinstr(v1,"Ù","U",.)
replace v1 = subinstr(v1,"Ü","U",.)
replace v1 = subinstr(v1,"ñ","n",.)
replace v1 = subinstr(v1,"ç","c",.)
replace v1 = subinstr(v1,"á","a",.)
replace v1 = subinstr(v1,"à","a",.)
replace v1 = subinstr(v1,"ä","a",.)
replace v1 = subinstr(v1,"è","e",.)
replace v1 = subinstr(v1,"é","e",.)
replace v1 = subinstr(v1,"í","i",.)
replace v1 = subinstr(v1,"ï","i",.)
replace v1 = subinstr(v1,"ó","o",.)
replace v1 = subinstr(v1,"ò","o",.)
replace v1 = subinstr(v1,"ö","o",.)
replace v1 = subinstr(v1,"ú","u",.)
replace v1 = subinstr(v1,"ù","u",.)
replace v1 = subinstr(v1,"ü","u",.)
replace v1 = subinstr(v1,"ª"," ",.)
replace v1 = subinstr(v1,"«","(",.)
replace v1 = subinstr(v1,"»",")",.)

* identify census districts
g is_cdis = (regexm(v1," distrito ") == 1)
g is_csec = (regexm(v1," seccion ") == 1)
g is_cmun = (is_cdis == 0 & is_csec == 0)

* obtain census tracts and districts
g CUSEC = substr(v1,1,10) if is_csec == 1
g ProvCode = substr(v1,1,2)
g MunCode = substr(v1,3,3)
g DistrictCode = substr(v1,6,2) if (is_cdis == 1|is_csec == 1)
g SectionCode = substr(v1,8,3) if (is_csec == 1)

* destring to merge
destring ProvCode, g(prov) force
destring MunCode, g(cmun) force
destring DistrictCode, g(cdis) force
destring SectionCode, g(csec) force
drop if prov==.

* municipality names
g MunName = ""
replace MunName = substr(v1,12,strpos(v1," seccion ")-12) if is_csec==1
replace MunName = substr(v1,9,strpos(v1," distrito ")-9) if is_cdis==1
replace MunName = substr(v1,7,length(v1)-6) if is_cmun==1

end 
}
********************************************************************************
* pre-celaning
********************************************************************************
{
	* Indicadores de renta media
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30824sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 7

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 MedianPersInc2017
rename v3 MedianPersInc2016
rename v4 MedianPersInc2015
rename v5 MedianHHInc2017
rename v6 MedianHHInc2016
rename v7 MedianHHInc2015
cap drop v8

* label
forval year = 2015(1)2017 {

	la var MedianPersInc`year' "Median Personal Income in `year'"
	la var MedianHHInc`year' "Median Household Income in `year'"

}

* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_income.dta", replace

	}
	* Distribución por fuente de ingresos
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30825sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 16

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 IncSourceWage2017
rename v3 IncSourceWage2016
rename v4 IncSourceWage2015
rename v5 IncSourcePens2017
rename v6 IncSourcePens2016
rename v7 IncSourcePens2015
rename v8 IncSourceUI2017
rename v9 IncSourceUI2016
rename v10 IncSourceUI2015
rename v11 IncSourceOtherSub2017
rename v12 IncSourceOtherSub2016
rename v13 IncSourceOtherSub2015
rename v14 IncSourceOtherInc2017
rename v15 IncSourceOtherInc2016
rename v16 IncSourceOtherInc2015
cap drop v17

* label
forval year = 2015(1)2017 {

	la var IncSourceWage`year' "Pct HHs with Wage as main income source in `year'"
	la var IncSourcePens`year' "Pct HHs with Pension as main income source in `year'"
	la var IncSourceUI`year' "Pct HHs with UI/UA as main income source in `year'"
	la var IncSourceOtherSub`year' "Pct HHs with Other Subsidies as main income source in `year'"
	la var IncSourceOtherInc`year' "Pct HHs with Other Income as main income source in `year'"
}

* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_SourceIncome.dta", replace

	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales fijos por sexo
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30826sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 28

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 PopIncB5000EUR2017
rename v3 PopIncB5000EUR2016
rename v4 PopIncB5000EUR2015
rename v5 PopIncB7500EUR2017
rename v6 PopIncB7500EUR2016
rename v7 PopIncB7500EUR2015
rename v8 PopIncB10000EUR2017
rename v9 PopIncB10000EUR2016
rename v10 PopIncB10000EUR2015
rename v11 MPopIncB5000EUR2017
rename v12 MPopIncB5000EUR2016
rename v13 MPopIncB5000EUR2015
rename v14 MPopIncB7500EUR2017
rename v15 MPopIncB7500EUR2016
rename v16 MPopIncB7500EUR2015
rename v17 MPopIncB10000EUR2017
rename v18 MPopIncB10000EUR2016
rename v19 MPopIncB10000EUR2015
rename v20 FPopIncB5000EUR2017
rename v21 FPopIncB5000EUR2016
rename v22 FPopIncB5000EUR2015
rename v23 FPopIncB7500EUR2017
rename v24 FPopIncB7500EUR2016
rename v25 FPopIncB7500EUR2015
rename v26 FPopIncB10000EUR2017
rename v27 FPopIncB10000EUR2016
rename v28 FPopIncB10000EUR2015

cap drop v29

* label
forval year = 2015(1)2017 {

	la var PopIncB5000EUR`year' "Share Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var PopIncB7500EUR`year' "Share Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var PopIncB10000EUR`year' "Share Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var MPopIncB5000EUR`year' "Share Male Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var MPopIncB7500EUR`year' "Share Male Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var MPopIncB10000EUR`year' "Share Male Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var FPopIncB5000EUR`year' "Share Female Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var FPopIncB7500EUR`year' "Share Female Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var FPopIncB10000EUR`year' "Share Female Pop with Inc per Consumption Unit below 10000 EUR in `year'"

}


* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_IncThrGender.dta", replace

	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales fijos por sexo y tramos de edad
	****************************************************************************
	{
* load
import delimited "$orig/ine/INE Atlas Renta/30827sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 82

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 PopL18IncB5000EUR2017
rename v3 PopL18IncB5000EUR2016
rename v4 PopL18IncB5000EUR2015
rename v5 PopL18IncB7500EUR2017
rename v6 PopL18IncB7500EUR2016
rename v7 PopL18IncB7500EUR2015
rename v8 PopL18IncB10000EUR2017
rename v9 PopL18IncB10000EUR2016
rename v10 PopL18IncB10000EUR2015
rename v11 Pop1864IncB5000EUR2017
rename v12 Pop1864IncB5000EUR2016
rename v13 Pop1864IncB5000EUR2015
rename v14 Pop1864IncB7500EUR2017
rename v15 Pop1864IncB7500EUR2016
rename v16 Pop1864IncB7500EUR2015
rename v17 Pop1864IncB10000EUR2017
rename v18 Pop1864IncB10000EUR2016
rename v19 Pop1864IncB10000EUR2015
rename v20 PopM65IncB5000EUR2017
rename v21 PopM65IncB5000EUR2016
rename v22 PopM65IncB5000EUR2015
rename v23 PopM65IncB7500EUR2017
rename v24 PopM65IncB7500EUR2016
rename v25 PopM65IncB7500EUR2015
rename v26 PopM65IncB10000EUR2017
rename v27 PopM65IncB10000EUR2016
rename v28 PopM65IncB10000EUR2015

rename v29 MPopL18IncB5000EUR2017
rename v30 MPopL18IncB5000EUR2016
rename v31 MPopL18IncB5000EUR2015
rename v32 MPopL18IncB7500EUR2017
rename v33 MPopL18IncB7500EUR2016
rename v34 MPopL18IncB7500EUR2015
rename v35 MPopL18IncB10000EUR2017
rename v36 MPopL18IncB10000EUR2016
rename v37 MPopL18IncB10000EUR2015
rename v38 MPop1864IncB5000EUR2017
rename v39 MPop1864IncB5000EUR2016
rename v40 MPop1864IncB5000EUR2015
rename v41 MPop1864IncB7500EUR2017
rename v42 MPop1864IncB7500EUR2016
rename v43 MPop1864IncB7500EUR2015
rename v44 MPop1864IncB10000EUR2017
rename v45 MPop1864IncB10000EUR2016
rename v46 MPop1864IncB10000EUR2015
rename v47 MPopM65IncB5000EUR2017
rename v48 MPopM65IncB5000EUR2016
rename v49 MPopM65IncB5000EUR2015
rename v50 MPopM65IncB7500EUR2017
rename v51 MPopM65IncB7500EUR2016
rename v52 MPopM65IncB7500EUR2015
rename v53 MPopM65IncB10000EUR2017
rename v54 MPopM65IncB10000EUR2016
rename v55 MPopM65IncB10000EUR2015

rename v56 FPopL18IncB5000EUR2017
rename v57 FPopL18IncB5000EUR2016
rename v58 FPopL18IncB5000EUR2015
rename v59 FPopL18IncB7500EUR2017
rename v60 FPopL18IncB7500EUR2016
rename v61 FPopL18IncB7500EUR2015
rename v62 FPopL18IncB10000EUR2017
rename v63 FPopL18IncB10000EUR2016
rename v64 FPopL18IncB10000EUR2015
rename v65 FPop1864IncB5000EUR2017
rename v66 FPop1864IncB5000EUR2016
rename v67 FPop1864IncB5000EUR2015
rename v68 FPop1864IncB7500EUR2017
rename v69 FPop1864IncB7500EUR2016
rename v70 FPop1864IncB7500EUR2015
rename v71 FPop1864IncB10000EUR2017
rename v72 FPop1864IncB10000EUR2016
rename v73 FPop1864IncB10000EUR2015
rename v74 FPopM65IncB5000EUR2017
rename v75 FPopM65IncB5000EUR2016
rename v76 FPopM65IncB5000EUR2015
rename v77 FPopM65IncB7500EUR2017
rename v78 FPopM65IncB7500EUR2016
rename v79 FPopM65IncB7500EUR2015
rename v80 FPopM65IncB10000EUR2017
rename v81 FPopM65IncB10000EUR2016
rename v82 FPopM65IncB10000EUR2015

cap drop v83

* label
forval year = 2015(1)2017 {

	la var PopL18IncB5000EUR`year' "Share Less 18yo Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var PopL18IncB7500EUR`year' "Share Less 18yo Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var PopL18IncB10000EUR`year' "Share Less 18yo Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var Pop1864IncB5000EUR`year' "Share 18-64 Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var Pop1864IncB7500EUR`year' "Share 18-64 Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var Pop1864IncB10000EUR`year' "Share 18-64 Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var PopM65IncB5000EUR`year' "Share 65+ Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var PopM65IncB7500EUR`year' "Share 65+ Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var PopM65IncB10000EUR`year' "Share 65+ Pop with Inc per Consumption Unit below 10000 EUR in `year'"

	la var MPopL18IncB5000EUR`year' "Share Less 18yo Male Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var MPopL18IncB7500EUR`year' "Share Less 18yo Male Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var MPopL18IncB10000EUR`year' "Share Less 18yo Male Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var MPop1864IncB5000EUR`year' "Share 18-64 Male Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var MPop1864IncB7500EUR`year' "Share 18-64 Male Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var MPop1864IncB10000EUR`year' "Share 18-64 Male Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var MPopM65IncB5000EUR`year' "Share 65+ Male Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var MPopM65IncB7500EUR`year' "Share 65+ Male Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var MPopM65IncB10000EUR`year' "Share 65+ Male Pop with Inc per Consumption Unit below 10000 EUR in `year'"

	la var FPopL18IncB5000EUR`year' "Share Less 18yo Female Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var FPopL18IncB7500EUR`year' "Share Less 18yo Female Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var FPopL18IncB10000EUR`year' "Share Less 18yo Female Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var FPop1864IncB5000EUR`year' "Share 18-64 Female Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var FPop1864IncB7500EUR`year' "Share 18-64 Female Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var FPop1864IncB10000EUR`year' "Share 18-64 Female Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var FPopM65IncB5000EUR`year' "Share 65+ Female Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var FPopM65IncB7500EUR`year' "Share 65+ Female Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var FPopM65IncB10000EUR`year' "Share 65+ Female Pop with Inc per Consumption Unit below 10000 EUR in `year'"

}

* common cleaning
common_cleaning

* store temporary data
compress
save "$data/temp/ine_atlas_renta_IncThrGenderAge.dta", replace

	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales fijos por sexo y nacionalidad
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30828sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 55

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 EspPopIncB5000EUR2017
rename v3 EspPopIncB5000EUR2016
rename v4 EspPopIncB5000EUR2015
rename v5 EspPopIncB7500EUR2017
rename v6 EspPopIncB7500EUR2016
rename v7 EspPopIncB7500EUR2015
rename v8 EspPopIncB10000EUR2017
rename v9 EspPopIncB10000EUR2016
rename v10 EspPopIncB10000EUR2015
rename v11 ForPopIncB5000EUR2017
rename v12 ForPopIncB5000EUR2016
rename v13 ForPopIncB5000EUR2015
rename v14 ForPopIncB7500EUR2017
rename v15 ForPopIncB7500EUR2016
rename v16 ForPopIncB7500EUR2015
rename v17 ForPopIncB10000EUR2017
rename v18 ForPopIncB10000EUR2016
rename v19 ForPopIncB10000EUR2015

rename v20 EspMPopIncB5000EUR2017
rename v21 EspMPopIncB5000EUR2016
rename v22 EspMPopIncB5000EUR2015
rename v23 EspMPopIncB7500EUR2017
rename v24 EspMPopIncB7500EUR2016
rename v25 EspMPopIncB7500EUR2015
rename v26 EspMPopIncB10000EUR2017
rename v27 EspMPopIncB10000EUR2016
rename v28 EspMPopIncB10000EUR2015
rename v29 ForMPopIncB5000EUR2017
rename v30 ForMPopIncB5000EUR2016
rename v31 ForMPopIncB5000EUR2015
rename v32 ForMPopIncB7500EUR2017
rename v33 ForMPopIncB7500EUR2016
rename v34 ForMPopIncB7500EUR2015
rename v35 ForMPopIncB10000EUR2017
rename v36 ForMPopIncB10000EUR2016
rename v37 ForMPopIncB10000EUR2015

rename v38 EspFPopIncB5000EUR2017
rename v39 EspFPopIncB5000EUR2016
rename v40 EspFPopIncB5000EUR2015
rename v41 EspFPopIncB7500EUR2017
rename v42 EspFPopIncB7500EUR2016
rename v43 EspFPopIncB7500EUR2015
rename v44 EspFPopIncB10000EUR2017
rename v45 EspFPopIncB10000EUR2016
rename v46 EspFPopIncB10000EUR2015
rename v47 ForFPopIncB5000EUR2017
rename v48 ForFPopIncB5000EUR2016
rename v49 ForFPopIncB5000EUR2015
rename v50 ForFPopIncB7500EUR2017
rename v51 ForFPopIncB7500EUR2016
rename v52 ForFPopIncB7500EUR2015
rename v53 ForFPopIncB10000EUR2017
rename v54 ForFPopIncB10000EUR2016
rename v55 ForFPopIncB10000EUR2015

cap drop v56

* label
forval year = 2015(1)2017 {

	la var EspPopIncB5000EUR`year' "Share Spanish Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var EspPopIncB7500EUR`year' "Share Spanish Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var EspPopIncB10000EUR`year' "Share Spanish Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var EspMPopIncB5000EUR`year' "Share Male Spanish Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var EspMPopIncB7500EUR`year' "Share Male Spanish Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var EspMPopIncB10000EUR`year' "Share Male Spanish Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var EspFPopIncB5000EUR`year' "Share Female Spanish Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var EspFPopIncB7500EUR`year' "Share Female Spanish Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var EspFPopIncB10000EUR`year' "Share Female Spanish Pop with Inc per Consumption Unit below 10000 EUR in `year'"

	la var ForPopIncB5000EUR`year' "Share Foreign Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var ForPopIncB7500EUR`year' "Share Foreign Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var ForPopIncB10000EUR`year' "Share Foreign Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var ForMPopIncB5000EUR`year' "Share Male Foreign Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var ForMPopIncB7500EUR`year' "Share Male Foreign Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var ForMPopIncB10000EUR`year' "Share Male Foreign Pop with Inc per Consumption Unit below 10000 EUR in `year'"
	la var ForFPopIncB5000EUR`year' "Share Female Foreign Pop with Inc per Consumption Unit below 5000 EUR in `year'"
	la var ForFPopIncB7500EUR`year' "Share Female Foreign Pop with Inc per Consumption Unit below 7500 EUR in `year'"
	la var ForFPopIncB10000EUR`year' "Share Female Foreign Pop with Inc per Consumption Unit below 10000 EUR in `year'"

}

** common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_IncThrGenderNat.dta", replace
	
	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales relativos por sexo
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30829sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 55

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 PopB40MedInc2017
rename v3 PopB40MedInc2016
rename v4 PopB40MedInc2015
rename v5 PopB50MedInc2017
rename v6 PopB50MedInc2016
rename v7 PopB50MedInc2015
rename v8 PopB60MedInc2017
rename v9 PopB60MedInc2016
rename v10 PopB60MedInc2015
rename v11 PopB140MedInc2017
rename v12 PopB140MedInc2016
rename v13 PopB140MedInc2015
rename v14 PopB160MedInc2017
rename v15 PopB160MedInc2016
rename v16 PopB160MedInc2015
rename v17 PopB200MedInc2017
rename v18 PopB200MedInc2016
rename v19 PopB200MedInc2015

rename v20 MPopB40MedInc2017
rename v21 MPopB40MedInc2016
rename v22 MPopB40MedInc2015
rename v23 MPopB50MedInc2017
rename v24 MPopB50MedInc2016
rename v25 MPopB50MedInc2015
rename v26 MPopB60MedInc2017
rename v27 MPopB60MedInc2016
rename v28 MPopB60MedInc2015
rename v29 MPopB140MedInc2017
rename v30 MPopB140MedInc2016
rename v31 MPopB140MedInc2015
rename v32 MPopB160MedInc2017
rename v33 MPopB160MedInc2016
rename v34 MPopB160MedInc2015
rename v35 MPopB200MedInc2017
rename v36 MPopB200MedInc2016
rename v37 MPopB200MedInc2015

rename v38 FPopB40MedInc2017
rename v39 FPopB40MedInc2016
rename v40 FPopB40MedInc2015
rename v41 FPopB50MedInc2017
rename v42 FPopB50MedInc2016
rename v43 FPopB50MedInc2015
rename v44 FPopB60MedInc2017
rename v45 FPopB60MedInc2016
rename v46 FPopB60MedInc2015
rename v47 FPopB140MedInc2017
rename v48 FPopB140MedInc2016
rename v49 FPopB140MedInc2015
rename v50 FPopB160MedInc2017
rename v51 FPopB160MedInc2016
rename v52 FPopB160MedInc2015
rename v53 FPopB200MedInc2017
rename v54 FPopB200MedInc2016
rename v55 FPopB200MedInc2015

cap drop v56

* label
forval year = 2015(1)2017 {

	la var PopB40MedInc`year' "Share Pop below 40% of Median Income in `year'"
	la var PopB50MedInc`year' "Share Pop below 50% of Median Income in `year'"
	la var PopB60MedInc`year' "Share Pop below 60% of Median Income in `year'"
	la var PopB140MedInc`year' "Share Pop below 140% of Median Income in `year'"
	la var PopB160MedInc`year' "Share Pop below 160% of Median Income in `year'"
	la var PopB200MedInc`year' "Share Pop below 200% of Median Income in `year'"

	la var MPopB40MedInc`year' "Share Male Pop below 40% of Median Income in `year'"
	la var MPopB50MedInc`year' "Share Male Pop below 50% of Median Income in `year'"
	la var MPopB60MedInc`year' "Share Male Pop below 60% of Median Income in `year'"
	la var MPopB140MedInc`year' "Share Male Pop below 140% of Median Income in `year'"
	la var MPopB160MedInc`year' "Share Male Pop below 160% of Median Income in `year'"
	la var MPopB200MedInc`year' "Share Male Pop below 200% of Median Income in `year'"

	la var FPopB40MedInc`year' "Share Female Pop below 40% of Median Income in `year'"
	la var FPopB50MedInc`year' "Share Female Pop below 50% of Median Income in `year'"
	la var FPopB60MedInc`year' "Share Female Pop below 60% of Median Income in `year'"
	la var FPopB140MedInc`year' "Share Female Pop below 140% of Median Income in `year'"
	la var FPopB160MedInc`year' "Share Female Pop below 160% of Median Income in `year'"
	la var FPopB200MedInc`year' "Share Female Pop below 200% of Median Income in `year'"

}
* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_RelIncThrGender.dta", replace

	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales relativos por sexo y tramos de edad
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30830sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 163

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 PopL18B40MedInc2017
rename v3 PopL18B40MedInc2016
rename v4 PopL18B40MedInc2015
rename v5 PopL18B50MedInc2017
rename v6 PopL18B50MedInc2016
rename v7 PopL18B50MedInc2015
rename v8 PopL18B60MedInc2017
rename v9 PopL18B60MedInc2016
rename v10 PopL18B60MedInc2015
rename v11 PopL18B140MedInc2017
rename v12 PopL18B140MedInc2016
rename v13 PopL18B140MedInc2015
rename v14 PopL18B160MedInc2017
rename v15 PopL18B160MedInc2016
rename v16 PopL18B160MedInc2015
rename v17 PopL18B200MedInc2017
rename v18 PopL18B200MedInc2016
rename v19 PopL18B200MedInc2015

rename v20 Pop1864B40MedInc2017
rename v21 Pop1864B40MedInc2016
rename v22 Pop1864B40MedInc2015
rename v23 Pop1864B50MedInc2017
rename v24 Pop1864B50MedInc2016
rename v25 Pop1864B50MedInc2015
rename v26 Pop1864B60MedInc2017
rename v27 Pop1864B60MedInc2016
rename v28 Pop1864B60MedInc2015
rename v29 Pop1864B140MedInc2017
rename v30 Pop1864B140MedInc2016
rename v31 Pop1864B140MedInc2015
rename v32 Pop1864B160MedInc2017
rename v33 Pop1864B160MedInc2016
rename v34 Pop1864B160MedInc2015
rename v35 Pop1864B200MedInc2017
rename v36 Pop1864B200MedInc2016
rename v37 Pop1864B200MedInc2015

rename v38 PopM65B40MedInc2017
rename v39 PopM65B40MedInc2016
rename v40 PopM65B40MedInc2015
rename v41 PopM65B50MedInc2017
rename v42 PopM65B50MedInc2016
rename v43 PopM65B50MedInc2015
rename v44 PopM65B60MedInc2017
rename v45 PopM65B60MedInc2016
rename v46 PopM65B60MedInc2015
rename v47 PopM65B140MedInc2017
rename v48 PopM65B140MedInc2016
rename v49 PopM65B140MedInc2015
rename v50 PopM65B160MedInc2017
rename v51 PopM65B160MedInc2016
rename v52 PopM65B160MedInc2015
rename v53 PopM65B200MedInc2017
rename v54 PopM65B200MedInc2016
rename v55 PopM65B200MedInc2015

rename v56 MPopL18B40MedInc2017
rename v57 MPopL18B40MedInc2016
rename v58 MPopL18B40MedInc2015
rename v59 MPopL18B50MedInc2017
rename v60 MPopL18B50MedInc2016
rename v61 MPopL18B50MedInc2015
rename v62 MPopL18B60MedInc2017
rename v63 MPopL18B60MedInc2016
rename v64 MPopL18B60MedInc2015
rename v65 MPopL18B140MedInc2017
rename v66 MPopL18B140MedInc2016
rename v67 MPopL18B140MedInc2015
rename v68 MPopL18B160MedInc2017
rename v69 MPopL18B160MedInc2016
rename v70 MPopL18B160MedInc2015
rename v71 MPopL18B200MedInc2017
rename v72 MPopL18B200MedInc2016
rename v73 MPopL18B200MedInc2015

rename v74 MPop1864B40MedInc2017
rename v75 MPop1864B40MedInc2016
rename v76 MPop1864B40MedInc2015
rename v77 MPop1864B50MedInc2017
rename v78 MPop1864B50MedInc2016
rename v79 MPop1864B50MedInc2015
rename v80 MPop1864B60MedInc2017
rename v81 MPop1864B60MedInc2016
rename v82 MPop1864B60MedInc2015
rename v83 MPop1864B140MedInc2017
rename v84 MPop1864B140MedInc2016
rename v85 MPop1864B140MedInc2015
rename v86 MPop1864B160MedInc2017
rename v87 MPop1864B160MedInc2016
rename v88 MPop1864B160MedInc2015
rename v89 MPop1864B200MedInc2017
rename v90 MPop1864B200MedInc2016
rename v91 MPop1864B200MedInc2015

rename v92 MPopM65B40MedInc2017
rename v93 MPopM65B40MedInc2016
rename v94 MPopM65B40MedInc2015
rename v95 MPopM65B50MedInc2017
rename v96 MPopM65B50MedInc2016
rename v97 MPopM65B50MedInc2015
rename v98 MPopM65B60MedInc2017
rename v99 MPopM65B60MedInc2016
rename v100 MPopM65B60MedInc2015
rename v101 MPopM65B140MedInc2017
rename v102 MPopM65B140MedInc2016
rename v103 MPopM65B140MedInc2015
rename v104 MPopM65B160MedInc2017
rename v105 MPopM65B160MedInc2016
rename v106 MPopM65B160MedInc2015
rename v107 MPopM65B200MedInc2017
rename v108 MPopM65B200MedInc2016
rename v109 MPopM65B200MedInc2015

rename v110 FPopL18B40MedInc2017
rename v111 FPopL18B40MedInc2016
rename v112 FPopL18B40MedInc2015
rename v113 FPopL18B50MedInc2017
rename v114 FPopL18B50MedInc2016
rename v115 FPopL18B50MedInc2015
rename v116 FPopL18B60MedInc2017
rename v117 FPopL18B60MedInc2016
rename v118 FPopL18B60MedInc2015
rename v119 FPopL18B140MedInc2017
rename v120 FPopL18B140MedInc2016
rename v121 FPopL18B140MedInc2015
rename v122 FPopL18B160MedInc2017
rename v123 FPopL18B160MedInc2016
rename v124 FPopL18B160MedInc2015
rename v125 FPopL18B200MedInc2017
rename v126 FPopL18B200MedInc2016
rename v127 FPopL18B200MedInc2015

rename v128 FPop1864B40MedInc2017
rename v129 FPop1864B40MedInc2016
rename v130 FPop1864B40MedInc2015
rename v131 FPop1864B50MedInc2017
rename v132 FPop1864B50MedInc2016
rename v133 FPop1864B50MedInc2015
rename v134 FPop1864B60MedInc2017
rename v135 FPop1864B60MedInc2016
rename v136 FPop1864B60MedInc2015
rename v137 FPop1864B140MedInc2017
rename v138 FPop1864B140MedInc2016
rename v139 FPop1864B140MedInc2015
rename v140 FPop1864B160MedInc2017
rename v141 FPop1864B160MedInc2016
rename v142 FPop1864B160MedInc2015
rename v143 FPop1864B200MedInc2017
rename v144 FPop1864B200MedInc2016
rename v145 FPop1864B200MedInc2015

rename v146 FPopM65B40MedInc2017
rename v147 FPopM65B40MedInc2016
rename v148 FPopM65B40MedInc2015
rename v149 FPopM65B50MedInc2017
rename v150 FPopM65B50MedInc2016
rename v151 FPopM65B50MedInc2015
rename v152 FPopM65B60MedInc2017
rename v153 FPopM65B60MedInc2016
rename v154 FPopM65B60MedInc2015
rename v155 FPopM65B140MedInc2017
rename v156 FPopM65B140MedInc2016
rename v157 FPopM65B140MedInc2015
rename v158 FPopM65B160MedInc2017
rename v159 FPopM65B160MedInc2016
rename v160 FPopM65B160MedInc2015
rename v161 FPopM65B200MedInc2017
rename v162 FPopM65B200MedInc2016
rename v163 FPopM65B200MedInc2015

cap drop v164

* label
forval year = 2015(1)2017 {

	la var PopL18B40MedInc`year' "Share Less 18yo Pop below 40% of Median Income in `year'"
	la var PopL18B50MedInc`year' "Share Less 18yo Pop below 50% of Median Income in `year'"
	la var PopL18B60MedInc`year' "Share Less 18yo Pop below 60% of Median Income in `year'"
	la var PopL18B140MedInc`year' "Share Less 18yo Pop below 140% of Median Income in `year'"
	la var PopL18B160MedInc`year' "Share Less 18yo Pop below 160% of Median Income in `year'"
	la var PopL18B200MedInc`year' "Share Less 18yo Pop below 200% of Median Income in `year'"

	la var Pop1864B40MedInc`year' "Share 18-64 Pop below 40% of Median Income in `year'"
	la var Pop1864B50MedInc`year' "Share 18-64 Pop below 50% of Median Income in `year'"
	la var Pop1864B60MedInc`year' "Share 18-64 Pop below 60% of Median Income in `year'"
	la var Pop1864B140MedInc`year' "Share 18-64 Pop below 140% of Median Income in `year'"
	la var Pop1864B160MedInc`year' "Share 18-64 Pop below 160% of Median Income in `year'"
	la var Pop1864B200MedInc`year' "Share 18-64 Pop below 200% of Median Income in `year'"

	la var PopM65B40MedInc`year' "Share 65+ Pop below 40% of Median Income in `year'"
	la var PopM65B50MedInc`year' "Share 65+ Pop below 50% of Median Income in `year'"
	la var PopM65B60MedInc`year' "Share 65+ Pop below 60% of Median Income in `year'"
	la var PopM65B140MedInc`year' "Share 65+ Pop below 140% of Median Income in `year'"
	la var PopM65B160MedInc`year' "Share 65+ Pop below 160% of Median Income in `year'"
	la var PopM65B200MedInc`year' "Share 65+ Pop below 200% of Median Income in `year'"

	la var MPopL18B40MedInc`year' "Share Less 18yo Male Pop below 40% of Median Income in `year'"
	la var MPopL18B50MedInc`year' "Share Less 18yo Male Pop below 50% of Median Income in `year'"
	la var MPopL18B60MedInc`year' "Share Less 18yo Male Pop below 60% of Median Income in `year'"
	la var MPopL18B140MedInc`year' "Share Less 18yo Male Pop below 140% of Median Income in `year'"
	la var MPopL18B160MedInc`year' "Share Less 18yo Male Pop below 160% of Median Income in `year'"
	la var MPopL18B200MedInc`year' "Share Less 18yo Male Pop below 200% of Median Income in `year'"

	la var MPop1864B40MedInc`year' "Share 18-64 Male Pop below 40% of Median Income in `year'"
	la var MPop1864B50MedInc`year' "Share 18-64 Male Pop below 50% of Median Income in `year'"
	la var MPop1864B60MedInc`year' "Share 18-64 Male Pop below 60% of Median Income in `year'"
	la var MPop1864B140MedInc`year' "Share 18-64 Male Pop below 140% of Median Income in `year'"
	la var MPop1864B160MedInc`year' "Share 18-64 Male Pop below 160% of Median Income in `year'"
	la var MPop1864B200MedInc`year' "Share 18-64 Male Pop below 200% of Median Income in `year'"

	la var MPopM65B40MedInc`year' "Share 65+ Male Pop below 40% of Median Income in `year'"
	la var MPopM65B50MedInc`year' "Share 65+ Male Pop below 50% of Median Income in `year'"
	la var MPopM65B60MedInc`year' "Share 65+ Male Pop below 60% of Median Income in `year'"
	la var MPopM65B140MedInc`year' "Share 65+ Male Pop below 140% of Median Income in `year'"
	la var MPopM65B160MedInc`year' "Share 65+ Male Pop below 160% of Median Income in `year'"
	la var MPopM65B200MedInc`year' "Share 65+ Male Pop below 200% of Median Income in `year'"

	la var FPopL18B40MedInc`year' "Share Less 18yo Female Pop below 40% of Median Income in `year'"
	la var FPopL18B50MedInc`year' "Share Less 18yo Female Pop below 50% of Median Income in `year'"
	la var FPopL18B60MedInc`year' "Share Less 18yo Female Pop below 60% of Median Income in `year'"
	la var FPopL18B140MedInc`year' "Share Less 18yo Female Pop below 140% of Median Income in `year'"
	la var FPopL18B160MedInc`year' "Share Less 18yo Female Pop below 160% of Median Income in `year'"
	la var FPopL18B200MedInc`year' "Share Less 18yo Female Pop below 200% of Median Income in `year'"

	la var FPop1864B40MedInc`year' "Share 18-64 Female Pop below 40% of Median Income in `year'"
	la var FPop1864B50MedInc`year' "Share 18-64 Female Pop below 50% of Median Income in `year'"
	la var FPop1864B60MedInc`year' "Share 18-64 Female Pop below 60% of Median Income in `year'"
	la var FPop1864B140MedInc`year' "Share 18-64 Female Pop below 140% of Median Income in `year'"
	la var FPop1864B160MedInc`year' "Share 18-64 Female Pop below 160% of Median Income in `year'"
	la var FPop1864B200MedInc`year' "Share 18-64 Female Pop below 200% of Median Income in `year'"

	la var FPopM65B40MedInc`year' "Share 65+ Female Pop below 40% of Median Income in `year'"
	la var FPopM65B50MedInc`year' "Share 65+ Female Pop below 50% of Median Income in `year'"
	la var FPopM65B60MedInc`year' "Share 65+ Female Pop below 60% of Median Income in `year'"
	la var FPopM65B140MedInc`year' "Share 65+ Female Pop below 140% of Median Income in `year'"
	la var FPopM65B160MedInc`year' "Share 65+ Female Pop below 160% of Median Income in `year'"
	la var FPopM65B200MedInc`year' "Share 65+ Female Pop below 200% of Median Income in `year'"

}

* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_RelIncThrGenderAge.dta", replace
	}
	* Porcentaje de población con ingresos por unidad de consumo por debajo de determinados umbrales relativos por sexo y nacionalidad
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30831sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 109

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 EspPopB40MedInc2017
rename v3 EspPopB40MedInc2016
rename v4 EspPopB40MedInc2015
rename v5 EspPopB50MedInc2017
rename v6 EspPopB50MedInc2016
rename v7 EspPopB50MedInc2015
rename v8 EspPopB60MedInc2017
rename v9 EspPopB60MedInc2016
rename v10 EspPopB60MedInc2015
rename v11 EspPopB140MedInc2017
rename v12 EspPopB140MedInc2016
rename v13 EspPopB140MedInc2015
rename v14 EspPopB160MedInc2017
rename v15 EspPopB160MedInc2016
rename v16 EspPopB160MedInc2015
rename v17 EspPopB200MedInc2017
rename v18 EspPopB200MedInc2016
rename v19 EspPopB200MedInc2015

rename v20 ForPopB40MedInc2017
rename v21 ForPopB40MedInc2016
rename v22 ForPopB40MedInc2015
rename v23 ForPopB50MedInc2017
rename v24 ForPopB50MedInc2016
rename v25 ForPopB50MedInc2015
rename v26 ForPopB60MedInc2017
rename v27 ForPopB60MedInc2016
rename v28 ForPopB60MedInc2015
rename v29 ForPopB140MedInc2017
rename v30 ForPopB140MedInc2016
rename v31 ForPopB140MedInc2015
rename v32 ForPopB160MedInc2017
rename v33 ForPopB160MedInc2016
rename v34 ForPopB160MedInc2015
rename v35 ForPopB200MedInc2017
rename v36 ForPopB200MedInc2016
rename v37 ForPopB200MedInc2015

rename v38 EspMPopB40MedInc2017
rename v39 EspMPopB40MedInc2016
rename v40 EspMPopB40MedInc2015
rename v41 EspMPopB50MedInc2017
rename v42 EspMPopB50MedInc2016
rename v43 EspMPopB50MedInc2015
rename v44 EspMPopB60MedInc2017
rename v45 EspMPopB60MedInc2016
rename v46 EspMPopB60MedInc2015
rename v47 EspMPopB140MedInc2017
rename v48 EspMPopB140MedInc2016
rename v49 EspMPopB140MedInc2015
rename v50 EspMPopB160MedInc2017
rename v51 EspMPopB160MedInc2016
rename v52 EspMPopB160MedInc2015
rename v53 EspMPopB200MedInc2017
rename v54 EspMPopB200MedInc2016
rename v55 EspMPopB200MedInc2015

rename v56 ForMPopB40MedInc2017
rename v57 ForMPopB40MedInc2016
rename v58 ForMPopB40MedInc2015
rename v59 ForMPopB50MedInc2017
rename v60 ForMPopB50MedInc2016
rename v61 ForMPopB50MedInc2015
rename v62 ForMPopB60MedInc2017
rename v63 ForMPopB60MedInc2016
rename v64 ForMPopB60MedInc2015
rename v65 ForMPopB140MedInc2017
rename v66 ForMPopB140MedInc2016
rename v67 ForMPopB140MedInc2015
rename v68 ForMPopB160MedInc2017
rename v69 ForMPopB160MedInc2016
rename v70 ForMPopB160MedInc2015
rename v71 ForMPopB200MedInc2017
rename v72 ForMPopB200MedInc2016
rename v73 ForMPopB200MedInc2015

rename v74 EspFPopB40MedInc2017
rename v75 EspFPopB40MedInc2016
rename v76 EspFPopB40MedInc2015
rename v77 EspFPopB50MedInc2017
rename v78 EspFPopB50MedInc2016
rename v79 EspFPopB50MedInc2015
rename v80 EspFPopB60MedInc2017
rename v81 EspFPopB60MedInc2016
rename v82 EspFPopB60MedInc2015
rename v83 EspFPopB140MedInc2017
rename v84 EspFPopB140MedInc2016
rename v85 EspFPopB140MedInc2015
rename v86 EspFPopB160MedInc2017
rename v87 EspFPopB160MedInc2016
rename v88 EspFPopB160MedInc2015
rename v89 EspFPopB200MedInc2017
rename v90 EspFPopB200MedInc2016
rename v91 EspFPopB200MedInc2015

rename v92 ForFPopB40MedInc2017
rename v93 ForFPopB40MedInc2016
rename v94 ForFPopB40MedInc2015
rename v95 ForFPopB50MedInc2017
rename v96 ForFPopB50MedInc2016
rename v97 ForFPopB50MedInc2015
rename v98 ForFPopB60MedInc2017
rename v99 ForFPopB60MedInc2016
rename v100 ForFPopB60MedInc2015
rename v101 ForFPopB140MedInc2017
rename v102 ForFPopB140MedInc2016
rename v103 ForFPopB140MedInc2015
rename v104 ForFPopB160MedInc2017
rename v105 ForFPopB160MedInc2016
rename v106 ForFPopB160MedInc2015
rename v107 ForFPopB200MedInc2017
rename v108 ForFPopB200MedInc2016
rename v109 ForFPopB200MedInc2015

cap drop v110

* label
forval year = 2015(1)2017 {

	la var EspPopB40MedInc`year' "Share Spanish Pop below 40% of Median Income in `year'"
	la var EspPopB50MedInc`year' "Share Spanish Pop below 50% of Median Income in `year'"
	la var EspPopB60MedInc`year' "Share Spanish Pop below 60% of Median Income in `year'"
	la var EspPopB140MedInc`year' "Share Spanish Pop below 140% of Median Income in `year'"
	la var EspPopB160MedInc`year' "Share Spanish Pop below 160% of Median Income in `year'"
	la var EspPopB200MedInc`year' "Share Spanish Pop below 200% of Median Income in `year'"

	la var EspMPopB40MedInc`year' "Share Spanish Male Pop below 40% of Median Income in `year'"
	la var EspMPopB50MedInc`year' "Share Spanish Male Pop below 50% of Median Income in `year'"
	la var EspMPopB60MedInc`year' "Share Spanish Male Pop below 60% of Median Income in `year'"
	la var EspMPopB140MedInc`year' "Share Spanish Male Pop below 140% of Median Income in `year'"
	la var EspMPopB160MedInc`year' "Share Spanish Male Pop below 160% of Median Income in `year'"
	la var EspMPopB200MedInc`year' "Share Spanish Male Pop below 200% of Median Income in `year'"

	la var EspFPopB40MedInc`year' "Share Spanish Female Pop below 40% of Median Income in `year'"
	la var EspFPopB50MedInc`year' "Share Spanish Female Pop below 50% of Median Income in `year'"
	la var EspFPopB60MedInc`year' "Share Spanish Female Pop below 60% of Median Income in `year'"
	la var EspFPopB140MedInc`year' "Share Spanish Female Pop below 140% of Median Income in `year'"
	la var EspFPopB160MedInc`year' "Share Spanish Female Pop below 160% of Median Income in `year'"
	la var EspFPopB200MedInc`year' "Share Spanish Female Pop below 200% of Median Income in `year'"

	la var ForPopB40MedInc`year' "Share Foreign Pop below 40% of Median Income in `year'"
	la var ForPopB50MedInc`year' "Share Foreign Pop below 50% of Median Income in `year'"
	la var ForPopB60MedInc`year' "Share Foreign Pop below 60% of Median Income in `year'"
	la var ForPopB140MedInc`year' "Share Foreign Pop below 140% of Median Income in `year'"
	la var ForPopB160MedInc`year' "Share Foreign Pop below 160% of Median Income in `year'"
	la var ForPopB200MedInc`year' "Share Foreign Pop below 200% of Median Income in `year'"

	la var ForMPopB40MedInc`year' "Share Foreign Male Pop below 40% of Median Income in `year'"
	la var ForMPopB50MedInc`year' "Share Foreign Male Pop below 50% of Median Income in `year'"
	la var ForMPopB60MedInc`year' "Share Foreign Male Pop below 60% of Median Income in `year'"
	la var ForMPopB140MedInc`year' "Share Foreign Male Pop below 140% of Median Income in `year'"
	la var ForMPopB160MedInc`year' "Share Foreign Male Pop below 160% of Median Income in `year'"
	la var ForMPopB200MedInc`year' "Share Foreign Male Pop below 200% of Median Income in `year'"

	la var ForFPopB40MedInc`year' "Share Foreign Female Pop below 40% of Median Income in `year'"
	la var ForFPopB50MedInc`year' "Share Foreign Female Pop below 50% of Median Income in `year'"
	la var ForFPopB60MedInc`year' "Share Foreign Female Pop below 60% of Median Income in `year'"
	la var ForFPopB140MedInc`year' "Share Foreign Female Pop below 140% of Median Income in `year'"
	la var ForFPopB160MedInc`year' "Share Foreign Female Pop below 160% of Median Income in `year'"
	la var ForFPopB200MedInc`year' "Share Foreign Female Pop below 200% of Median Income in `year'"

}

* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_RelIncThrGenderNat.dta", replace

	}
	* Indicadores demográficos
	****************************************************************************
	{
* load data
import delimited "$orig/ine/INE Atlas Renta/30832sc.csv", delimiter(";") encoding(UTF-8) stringcols(_all) varnames(nonames) clear

local firstval 2
local lastval 19

* replace problematic characters
forval i = `firstval'(1)`lastval' {

	* if there is a "." -> remove it (these are thousands)
	replace v`i' = subinstr(v`i', ".", "",.) 
	* if there is a "," -> replace by . (these are commas)
	replace v`i' = subinstr(v`i', ",", ".",.) 

}

* destring
destring v`firstval'-v`lastval', force replace

* rename
rename v2 PopMeanAge2017
rename v3 PopMeanAge2016
rename v4 PopMeanAge2015
rename v5 PctBelow18yo2017
rename v6 PctBelow18yo2016
rename v7 PctBelow18yo2015
rename v8 PctAbove65yo2017
rename v9 PctAbove65yo2016
rename v10 PctAbove65yo2015
rename v11 MeanHHSize2017
rename v12 MeanHHSize2016
rename v13 MeanHHSize2015
rename v14 Pct1memberHH2017
rename v15 Pct1memberHH2016
rename v16 Pct1memberHH2015
rename v17 Pop2017
rename v18 Pop2016
rename v19 Pop2015
cap drop v20

* label
forval year = 2015(1)2017 {

	la var PopMeanAge`year' "Mean Age of the Population in `year'"
	la var PctBelow18yo`year' "Pct pop younger than 18 in `year'"
	la var PctAbove65yo`year' "Pct pop older than 65 in `year'"
	la var MeanHHSize`year' "Mean HH Size in `year'"
	la var Pct1memberHH`year' "Pct of HH with one member in `year'"
	la var Pop`year' "Population in `year'"

}

* common cleaning
common_cleaning

* store temp data
compress
save "$data/temp/ine_atlas_renta_demographics.dta", replace

	}		
}
********************************************************************************
* put data together
********************************************************************************
{
clear

* load first dataset
use "$data/temp/ine_atlas_renta_income.dta"

* merge
local identifiers is_cdis is_csec is_cmun CUSEC ProvCode prov cmun cdis csec

#d;
	local atlaslist SourceIncome IncThrGender IncThrGenderAge IncThrGenderNat
	RelIncThrGender RelIncThrGenderAge RelIncThrGenderNat demographics
	;
#d cr

foreach atlas in `atlaslist' {

	merge 1:1 `identifiers' using "$data/temp/ine_atlas_renta_`atlas'.dta"
	assert _m!=2
	drop _m

}

* label
drop v1
la var is_cdis "Is a Census District"
la var is_csec "Is a Census Tract"
la var is_cmun "Is a Municipality"
la var CUSEC "Census Tract"
la var ProvCode "Province Code"
la var prov "Province Code"
la var MunCode "Municipality Code"
la var cmun "Municipality Code"
la var DistrictCode "District Code"
la var cdis "District Code"
la var SectionCode "Section Code"
la var csec "Section Code"
la var MunName "Municipality Name"

rename ProvCode CPRO
rename MunCode CMUN
rename DistrictCode CDIS
rename SectionCode CSEC

* order
order is_cdis is_csec is_cmun CUSEC CPRO prov MunName CMUN cmun CDIS cdis CSEC csec
compress
save "$data/int/ine_atlas_renta.dta", replace

* reshape long
foreach agglevel in csec cdis cmun {

	use "$data/int/ine_atlas_renta.dta", clear

	keep if is_`agglevel'==1
	drop is_*

	* variables to reshape
#d;
	local reshapevars MedianPersInc MedianHHInc IncSourceWage 
	IncSourcePens IncSourceUI IncSourceOtherSub IncSourceOtherInc 
	PopIncB5000EUR PopIncB7500EUR PopIncB10000EUR MPopIncB5000EUR MPopIncB7500EUR 
	MPopIncB10000EUR FPopIncB5000EUR FPopIncB7500EUR FPopIncB10000EUR 
	PopL18IncB5000EUR PopL18IncB7500EUR PopL18IncB10000EUR Pop1864IncB5000EUR 
	Pop1864IncB7500EUR Pop1864IncB10000EUR PopM65IncB5000EUR PopM65IncB7500EUR 
	PopM65IncB10000EUR MPopL18IncB5000EUR MPopL18IncB7500EUR
	MPopL18IncB10000EUR MPop1864IncB5000EUR MPop1864IncB7500EUR
	MPop1864IncB10000EUR MPopM65IncB5000EUR MPopM65IncB7500EUR
	MPopM65IncB10000EUR FPopL18IncB5000EUR FPopL18IncB7500EUR FPopL18IncB10000EUR
	FPop1864IncB5000EUR FPop1864IncB7500EUR FPop1864IncB10000EUR FPopM65IncB5000EUR
	FPopM65IncB7500EUR FPopM65IncB10000EUR EspPopIncB5000EUR EspPopIncB7500EUR
	EspPopIncB10000EUR ForPopIncB5000EUR ForPopIncB7500EUR ForPopIncB10000EUR
	EspMPopIncB5000EUR EspMPopIncB7500EUR EspMPopIncB10000EUR ForMPopIncB5000EUR
	ForMPopIncB7500EUR ForMPopIncB10000EUR EspFPopIncB5000EUR EspFPopIncB7500EUR
	EspFPopIncB10000EUR ForFPopIncB5000EUR ForFPopIncB7500EUR ForFPopIncB10000EUR PopB40MedInc PopB50MedInc
	PopB60MedInc PopB140MedInc PopB160MedInc PopB200MedInc MPopB40MedInc
	MPopB50MedInc MPopB60MedInc MPopB140MedInc MPopB160MedInc MPopB200MedInc 
	FPopB40MedInc FPopB50MedInc FPopB60MedInc FPopB140MedInc FPopB160MedInc 
	FPopB200MedInc PopL18B40MedInc PopL18B50MedInc PopL18B60MedInc
	PopL18B140MedInc PopL18B160MedInc PopL18B200MedInc Pop1864B40MedInc
	Pop1864B50MedInc Pop1864B60MedInc Pop1864B140MedInc Pop1864B160MedInc
	Pop1864B200MedInc PopM65B40MedInc PopM65B50MedInc PopM65B60MedInc
	PopM65B140MedInc PopM65B160MedInc PopM65B200MedInc MPopL18B40MedInc
	MPopL18B50MedInc MPopL18B60MedInc MPopL18B140MedInc MPopL18B160MedInc
	MPopL18B200MedInc MPop1864B40MedInc MPop1864B50MedInc MPop1864B60MedInc
	MPop1864B140MedInc MPop1864B160MedInc MPop1864B200MedInc MPopM65B40MedInc
	MPopM65B50MedInc MPopM65B60MedInc MPopM65B140MedInc MPopM65B160MedInc
	MPopM65B200MedInc FPopL18B40MedInc FPopL18B50MedInc FPopL18B60MedInc
	FPopL18B140MedInc FPopL18B160MedInc FPopL18B200MedInc FPop1864B40MedInc
	FPop1864B50MedInc FPop1864B60MedInc FPop1864B140MedInc FPop1864B160MedInc
	FPop1864B200MedInc FPopM65B40MedInc FPopM65B50MedInc FPopM65B60MedInc
	FPopM65B140MedInc FPopM65B160MedInc FPopM65B200MedInc EspPopB40MedInc
	EspPopB50MedInc EspPopB60MedInc EspPopB140MedInc EspPopB160MedInc EspPopB200MedInc
	ForPopB40MedInc ForPopB50MedInc ForPopB60MedInc ForPopB140MedInc ForPopB160MedInc 
	ForPopB200MedInc EspMPopB40MedInc EspMPopB50MedInc EspMPopB60MedInc EspMPopB140MedInc
	EspMPopB160MedInc EspMPopB200MedInc ForMPopB40MedInc ForMPopB50MedInc
	ForMPopB60MedInc ForMPopB140MedInc ForMPopB160MedInc ForMPopB200MedInc
	EspFPopB40MedInc EspFPopB50MedInc EspFPopB60MedInc EspFPopB140MedInc
	EspFPopB160MedInc EspFPopB200MedInc ForFPopB40MedInc ForFPopB50MedInc
	ForFPopB60MedInc ForFPopB140MedInc ForFPopB160MedInc ForFPopB200MedInc
	PopMeanAge PctBelow18yo PctAbove65yo MeanHHSize Pct1memberHH Pop
	;
#d cr

	* level vars
#d;
	local ivars CUSEC CPRO prov MunName CMUN cmun 
	CDIS cdis CSEC csec
	;
#d cr

	* reshape
	reshape long `reshapevars', i(`ivars') j(Year)


	if "`aglevel'" == "cdis" {
	
		drop SectionCode csec CUSEC
	
	}
	else if "`aglevel'" == "cmun" {
	
		drop SectionCode csec CUSEC cdis CDIS
	
	}

* store
compress
save "$data/int/ine_atlas_renta_`agglevel'.dta", replace

}

}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear
}
